/*
Navicat MySQL Data Transfer

Source Server         : mySQL
Source Server Version : 50548
Source Host           : localhost:3306
Source Database       : test

Target Server Type    : MYSQL
Target Server Version : 60099
File Encoding         : 65001

Date: 2021-03-12 14:56:48
*/

SET FOREIGN_KEY_CHECKS=0;

-- ----------------------------
-- Table structure for `asp_book`
-- ----------------------------
DROP TABLE IF EXISTS `asp_book`;
CREATE TABLE `asp_book` (
`B_ID`  int(11) NOT NULL AUTO_INCREMENT ,
`B_NAME`  varchar(10) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT '账簿' COMMENT '账簿名' ,
`B_CREATEDATE`  int(8) NOT NULL COMMENT '账簿创建日期' ,
`B_DEFAULT`  bit(1) NOT NULL DEFAULT b'0' COMMENT '1表示默认账簿，一个用户只允许存在一个默认账簿' ,
`DELETE`  bit(1) NOT NULL DEFAULT b'0' COMMENT '删除标志，1表示已删除' ,
`U_ID`  int(11) NOT NULL COMMENT '用户ID' ,
`B_KIND`  int(10) NOT NULL COMMENT '账簿类型，例：日常生活、学生时代...' ,
PRIMARY KEY (`B_ID`),
FOREIGN KEY (`B_KIND`) REFERENCES `asp_kind_book` (`ID`) ON DELETE RESTRICT ON UPDATE RESTRICT,
FOREIGN KEY (`U_ID`) REFERENCES `asp_user` (`U_ID`) ON DELETE RESTRICT ON UPDATE RESTRICT,
INDEX `FK_ASP_BOOK_UID` (`U_ID`) USING BTREE ,
INDEX `FK_ASP_BOOK_BKIND` (`B_KIND`) USING BTREE 
)
ENGINE=InnoDB
DEFAULT CHARACTER SET=utf8 COLLATE=utf8_general_ci
AUTO_INCREMENT=1

;

-- ----------------------------
-- Records of asp_book
-- ----------------------------
BEGIN;
COMMIT;

-- ----------------------------
-- Table structure for `asp_calendar`
-- ----------------------------
DROP TABLE IF EXISTS `asp_calendar`;
CREATE TABLE `asp_calendar` (
`C_ID`  int(11) NOT NULL AUTO_INCREMENT ,
`C_NAME`  varchar(10) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '日程名称' ,
`C_FLAG`  enum('时刻','时间段','全天') CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT '全天' COMMENT '1|全天；2|时间段；3|时刻' ,
`C_DATE`  int(8) NOT NULL ,
`C_START`  time NOT NULL DEFAULT '00:00:00' COMMENT '开始时间' ,
`C_END`  time NOT NULL DEFAULT '23:59:59' COMMENT '结束时间' ,
`C_KEEP`  int(11) NOT NULL DEFAULT 1 COMMENT '1|一次；0|每年；-1|每月；-2|每周；-3|每天；正整数表示持续几年' ,
`C_TEXT`  text CHARACTER SET utf8 COLLATE utf8_general_ci NULL COMMENT '备注' ,
`U_ID`  int(11) NOT NULL COMMENT '用户ID，外键' ,
`DELETE`  bit(1) NOT NULL DEFAULT b'0' COMMENT '删除标志，1表示已删除' ,
PRIMARY KEY (`C_ID`),
FOREIGN KEY (`U_ID`) REFERENCES `asp_user` (`U_ID`) ON DELETE RESTRICT ON UPDATE RESTRICT,
INDEX `FK_CALENDAR_UID` (`U_ID`) USING BTREE 
)
ENGINE=InnoDB
DEFAULT CHARACTER SET=utf8 COLLATE=utf8_general_ci
AUTO_INCREMENT=1

;

-- ----------------------------
-- Records of asp_calendar
-- ----------------------------
BEGIN;
COMMIT;

-- ----------------------------
-- Table structure for `asp_kind_book`
-- ----------------------------
DROP TABLE IF EXISTS `asp_kind_book`;
CREATE TABLE `asp_kind_book` (
`ID`  int(11) NOT NULL AUTO_INCREMENT COMMENT 'ID由系统生成' ,
`NAME`  varchar(10) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '类型名称' ,
`DELETE`  bit(1) NOT NULL DEFAULT b'0' ,
PRIMARY KEY (`ID`)
)
ENGINE=InnoDB
DEFAULT CHARACTER SET=utf8 COLLATE=utf8_general_ci
AUTO_INCREMENT=2

;

-- ----------------------------
-- Records of asp_kind_book
-- ----------------------------
BEGIN;
INSERT INTO `asp_kind_book` VALUES ('1', '学生时代', '');
COMMIT;

-- ----------------------------
-- Table structure for `asp_kind_kind`
-- ----------------------------
DROP TABLE IF EXISTS `asp_kind_kind`;
CREATE TABLE `asp_kind_kind` (
`K_ID`  int(11) NOT NULL AUTO_INCREMENT COMMENT '支付用途：水果、服饰、书籍...' ,
`K_NAME`  varchar(10) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '名称' ,
`K_FLAG`  enum('收入','支出') CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT '支出' COMMENT '1|支出；2|收入' ,
`K_B_ID`  int(11) NOT NULL DEFAULT '-1' COMMENT '账簿类型，0|通用；-1|用户新建' ,
`U_ID`  int(11) NULL DEFAULT NULL COMMENT '用户ID，空值表示非用户创建' ,
`DELETE`  bit(1) NOT NULL DEFAULT b'0' COMMENT '删除标志，1表示删除' ,
PRIMARY KEY (`K_ID`)
)
ENGINE=InnoDB
DEFAULT CHARACTER SET=utf8 COLLATE=utf8_general_ci
AUTO_INCREMENT=14

;

-- ----------------------------
-- Records of asp_kind_kind
-- ----------------------------
BEGIN;
INSERT INTO `asp_kind_kind` VALUES ('1', '话费网费', '支出', '1', null, ''), ('2', '水果', '支出', '1', null, ''), ('3', '餐饮', '支出', '1', null, ''), ('4', '服饰鞋帽', '支出', '1', null, ''), ('5', '住宿费用', '支出', '1', null, ''), ('6', '班费社费', '支出', '1', null, ''), ('7', '学习', '支出', '1', null, ''), ('8', '数码', '支出', '1', null, ''), ('9', '生活费', '收入', '1', null, ''), ('10', '奖学金', '收入', '1', null, ''), ('11', '科研收入', '收入', '1', null, ''), ('12', '借钱', '支出', '0', null, ''), ('13', '还钱', '收入', '0', null, '');
COMMIT;

-- ----------------------------
-- Table structure for `asp_kind_pay`
-- ----------------------------
DROP TABLE IF EXISTS `asp_kind_pay`;
CREATE TABLE `asp_kind_pay` (
`P_ID`  int(11) NOT NULL AUTO_INCREMENT ,
`P_NAME`  varchar(10) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '名称：现金、支付宝...' ,
`U_ID`  int(11) NULL DEFAULT NULL COMMENT '用户ID，空值表示非用户创建' ,
`DELETE`  bit(1) NOT NULL DEFAULT b'0' COMMENT '删除表示，1表示已删除' ,
PRIMARY KEY (`P_ID`)
)
ENGINE=InnoDB
DEFAULT CHARACTER SET=utf8 COLLATE=utf8_general_ci
AUTO_INCREMENT=5

;

-- ----------------------------
-- Records of asp_kind_pay
-- ----------------------------
BEGIN;
INSERT INTO `asp_kind_pay` VALUES ('1', '现金', null, ''), ('2', '银行卡', null, ''), ('3', '微信', null, ''), ('4', '支付宝', null, '');
COMMIT;

-- ----------------------------
-- Table structure for `asp_kind_text`
-- ----------------------------
DROP TABLE IF EXISTS `asp_kind_text`;
CREATE TABLE `asp_kind_text` (
`T_ID`  int(11) NOT NULL AUTO_INCREMENT ,
`T_NAME`  varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT 'default' COMMENT '分组名称' ,
`T_TEXT`  text CHARACTER SET utf8 COLLATE utf8_general_ci NULL COMMENT '备注' ,
`T_DEFAULT`  bit(1) NOT NULL DEFAULT b'0' COMMENT '是否为默认分组，1|是；0|不是' ,
`U_ID`  int(11) NOT NULL COMMENT '用户ID' ,
`T_COLOR`  char(15) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT 'white' COMMENT '分组背景色' ,
`DELETE`  bit(1) NOT NULL DEFAULT b'0' COMMENT '删除标志；1|删除；0|未删除' ,
PRIMARY KEY (`T_ID`),
FOREIGN KEY (`U_ID`) REFERENCES `asp_user` (`U_ID`) ON DELETE RESTRICT ON UPDATE RESTRICT,
INDEX `U_ID` (`U_ID`, `T_ID`) USING BTREE 
)
ENGINE=InnoDB
DEFAULT CHARACTER SET=utf8 COLLATE=utf8_general_ci
AUTO_INCREMENT=4

;

-- ----------------------------
-- Records of asp_kind_text
-- ----------------------------
BEGIN;
INSERT INTO `asp_kind_text` VALUES ('1', '未分类', null, '', '1', 'white', ''), ('2', '未分类', null, '', '2', 'white', ''), ('3', '笑死', '笑死，企鹅肉', '', '2', 'white', '');
COMMIT;

-- ----------------------------
-- Table structure for `asp_statement`
-- ----------------------------
DROP TABLE IF EXISTS `asp_statement`;
CREATE TABLE `asp_statement` (
`S_ID`  int(11) NOT NULL AUTO_INCREMENT COMMENT 'id，主键' ,
`S_NUMBER`  double(10,2) NOT NULL DEFAULT 0.00 COMMENT '金额' ,
`S_FLAG`  enum('收入','支出') CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT '支出' COMMENT '收入|支出' ,
`S_DATE`  int(8) NOT NULL COMMENT '日期' ,
`S_KIND`  varchar(10) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '用途：水果、服饰、鞋帽' ,
`S_PAY`  varchar(10) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '支付方式：支付宝、现金' ,
`S_TEXT`  text CHARACTER SET utf8 COLLATE utf8_general_ci NULL COMMENT '备注' ,
`DELETE`  bit(1) NOT NULL DEFAULT b'0' COMMENT '删除标志，1为删除' ,
`U_ID`  int(11) NOT NULL ,
`B_ID`  int(11) NOT NULL ,
PRIMARY KEY (`S_ID`),
FOREIGN KEY (`B_ID`) REFERENCES `asp_book` (`B_ID`) ON DELETE RESTRICT ON UPDATE RESTRICT,
FOREIGN KEY (`U_ID`) REFERENCES `asp_user` (`U_ID`) ON DELETE RESTRICT ON UPDATE RESTRICT,
INDEX `FK_STATEMENT_UID` (`U_ID`) USING BTREE ,
INDEX `FK_STATEMENT_BID` (`B_ID`) USING BTREE 
)
ENGINE=InnoDB
DEFAULT CHARACTER SET=utf8 COLLATE=utf8_general_ci
AUTO_INCREMENT=1

;

-- ----------------------------
-- Records of asp_statement
-- ----------------------------
BEGIN;
COMMIT;

-- ----------------------------
-- Table structure for `asp_text`
-- ----------------------------
DROP TABLE IF EXISTS `asp_text`;
CREATE TABLE `asp_text` (
`T_ID`  int(11) NOT NULL AUTO_INCREMENT ,
`T_DATE`  int(8) NOT NULL COMMENT '发布日期' ,
`T_TIME`  time NOT NULL COMMENT '发布时间' ,
`T_TEXT`  text CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '内容' ,
`T_F_ID`  int(11) NULL DEFAULT NULL COMMENT '回复对象ID，空值表示非评论' ,
`T_FLAG`  bit(1) NOT NULL DEFAULT b'0' COMMENT '是否存在评论回复，1表示存在' ,
`K_T_ID`  int(11) NOT NULL COMMENT '分组' ,
`U_ID`  int(11) NOT NULL COMMENT '用户ID' ,
`DELETE`  bit(1) NULL DEFAULT b'0' COMMENT '删除标志，1表示已删除' ,
PRIMARY KEY (`T_ID`),
FOREIGN KEY (`U_ID`, `K_T_ID`) REFERENCES `asp_kind_text` (`U_ID`, `T_ID`) ON DELETE RESTRICT ON UPDATE RESTRICT,
INDEX `FK_TEXT_KTID` (`K_T_ID`) USING BTREE ,
INDEX `FK_TEXT_UID` (`U_ID`, `K_T_ID`) USING BTREE 
)
ENGINE=InnoDB
DEFAULT CHARACTER SET=utf8 COLLATE=utf8_general_ci
AUTO_INCREMENT=4

;

-- ----------------------------
-- Records of asp_text
-- ----------------------------
BEGIN;
INSERT INTO `asp_text` VALUES ('1', '20210219', '15:01:28', '想吃饺子！', null, '', '2', '2', ''), ('2', '20210219', '15:30:25', '还有可乐！！！', '1', '', '2', '2', ''), ('3', '20210220', '00:02:15', '哈哈哈哈哈哈笑死，企鹅肉！！！', null, '', '3', '2', '');
COMMIT;

-- ----------------------------
-- Table structure for `asp_user`
-- ----------------------------
DROP TABLE IF EXISTS `asp_user`;
CREATE TABLE `asp_user` (
`U_ID`  int(11) NOT NULL AUTO_INCREMENT COMMENT '用户ID，由系统生成' ,
`U_LOGIN`  varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '登录标识，不允许重复' ,
`U_NAME`  varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '用户昵称' ,
`U_PWD`  varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '登录密码' ,
`U_PLANET`  varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT '小小星球' COMMENT '星球名字' ,
`U_CREATEDATE`  int(8) NOT NULL COMMENT '用户创建日期，不可更改' ,
`U_BIRTHDAY`  int(8) NULL DEFAULT NULL ,
`U_SEX`  enum('女','男','保密') CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT '保密' COMMENT '性别' ,
`U_STATUS`  varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '个性签名' ,
`DELETE`  bit(1) NULL DEFAULT b'0' COMMENT '删除标志，1表示已删除' ,
PRIMARY KEY (`U_ID`)
)
ENGINE=InnoDB
DEFAULT CHARACTER SET=utf8 COLLATE=utf8_general_ci
AUTO_INCREMENT=3

;

-- ----------------------------
-- Records of asp_user
-- ----------------------------
BEGIN;
INSERT INTO `asp_user` VALUES ('1', 'admin', 'admin', '000000', '小小星球', '20210219', null, '保密', '系统管理员', ''), ('2', 'sa', '穿裤衩的banana', '123', '爱哭的小火星', '20210219', '19990101', '保密', '什么时候开花呢', '');
COMMIT;

-- ----------------------------
-- Table structure for `author`
-- ----------------------------
DROP TABLE IF EXISTS `author`;
CREATE TABLE `author` (
`id`  int(11) NOT NULL AUTO_INCREMENT ,
`name`  varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL ,
`phone`  varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL ,
PRIMARY KEY (`id`)
)
ENGINE=InnoDB
DEFAULT CHARACTER SET=utf8 COLLATE=utf8_general_ci
AUTO_INCREMENT=4

;

-- ----------------------------
-- Records of author
-- ----------------------------
BEGIN;
INSERT INTO `author` VALUES ('1', 'joe', '123456'), ('3', 'smith', '123456');
COMMIT;

-- ----------------------------
-- Table structure for `blog`
-- ----------------------------
DROP TABLE IF EXISTS `blog`;
CREATE TABLE `blog` (
`id`  int(11) NOT NULL AUTO_INCREMENT ,
`title`  varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL ,
`content`  text CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL ,
`dob`  date NOT NULL ,
`state`  varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL ,
`author_id`  int(11) NULL DEFAULT NULL ,
PRIMARY KEY (`id`),
FOREIGN KEY (`author_id`) REFERENCES `author` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT,
INDEX `author_id` (`author_id`) USING BTREE 
)
ENGINE=InnoDB
DEFAULT CHARACTER SET=utf8 COLLATE=utf8_general_ci
AUTO_INCREMENT=3

;

-- ----------------------------
-- Records of blog
-- ----------------------------
BEGIN;
INSERT INTO `blog` VALUES ('1', 'a blog', 'blog content', '2017-10-24', 'active', '1'), ('2', 'a blog 2', 'a blog 2 cotent', '2018-04-03', 'active', '1');
COMMIT;

-- ----------------------------
-- Table structure for `bookkeeping`
-- ----------------------------
DROP TABLE IF EXISTS `bookkeeping`;
CREATE TABLE `bookkeeping` (
`id`  int(11) NOT NULL AUTO_INCREMENT ,
`count`  double NOT NULL ,
`date`  datetime NOT NULL ,
`kind`  varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL ,
`method`  varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL ,
`text`  text CHARACTER SET utf8 COLLATE utf8_general_ci NULL ,
`flag`  enum('2','1') CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT '1' COMMENT '1入账,2支出' ,
PRIMARY KEY (`id`),
FOREIGN KEY (`kind`) REFERENCES `paykind` (`kind_name`) ON DELETE RESTRICT ON UPDATE RESTRICT,
FOREIGN KEY (`method`) REFERENCES `paymethod` (`method_name`) ON DELETE RESTRICT ON UPDATE RESTRICT,
INDEX `fk_method` (`method`) USING BTREE ,
INDEX `fk_kind` (`kind`) USING BTREE 
)
ENGINE=InnoDB
DEFAULT CHARACTER SET=utf8 COLLATE=utf8_general_ci
AUTO_INCREMENT=15

;

-- ----------------------------
-- Records of bookkeeping
-- ----------------------------
BEGIN;
INSERT INTO `bookkeeping` VALUES ('1', '250', '2020-11-12 10:07:13', '奖金', '云闪付', '1', '1'), ('2', '358', '2020-11-30 10:08:08', '电费', '微信', null, '2'), ('3', '5', '2020-11-05 10:08:34', '水果啊啊啊', '现金', null, '2'), ('4', '23', '2020-12-02 09:40:22', '水果啊啊啊', '现金', '1', '2'), ('5', '50', '2020-12-02 09:49:31', '住宿', '微信', '', '2'), ('6', '50', '2020-12-02 09:49:31', '彩票中奖', '现金', '', '2'), ('7', '60', '2020-12-02 09:50:27', '奖金', '微信', '2', '2'), ('8', '60', '2020-12-02 09:50:27', '水果啊啊啊', '微信', '2', '2'), ('9', '34', '2020-12-02 14:48:40', '住宿', '现金', '好像有哪里不对', '2'), ('11', '65', '2020-12-01 14:49:40', '零食', '现金', '', '2'), ('14', '45', '2020-12-03 09:20:35', '彩票中奖', '支付宝', '', '1');
COMMIT;

-- ----------------------------
-- Table structure for `cleanmine`
-- ----------------------------
DROP TABLE IF EXISTS `cleanmine`;
CREATE TABLE `cleanmine` (
`win`  int(11) NOT NULL DEFAULT 0 COMMENT '统计总共胜局' ,
`total`  int(11) NOT NULL DEFAULT 0 COMMENT '统计总共玩的局数' ,
`minimum`  int(11) NULL DEFAULT NULL COMMENT '胜利一局所需要的最短时间,单位为秒' ,
`date`  date NULL DEFAULT NULL COMMENT '达成最短时间记录的日期' ,
`name`  varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT '' COMMENT '创纪录者姓名' ,
`winning_streak`  int(11) NOT NULL DEFAULT 0 COMMENT '最高连胜纪录' ,
`losing_streak`  int(11) NOT NULL DEFAULT 0 COMMENT '最高连败纪录' ,
`level`  char(20) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT '' COMMENT '级别,分为初级中级高级' ,
PRIMARY KEY (`level`)
)
ENGINE=InnoDB
DEFAULT CHARACTER SET=utf8 COLLATE=utf8_general_ci

;

-- ----------------------------
-- Records of cleanmine
-- ----------------------------
BEGIN;
INSERT INTO `cleanmine` VALUES ('0', '0', null, null, '', '0', '0', 'high'), ('0', '0', null, null, '', '0', '0', 'juinor'), ('0', '0', null, null, '', '0', '0', 'middle');
COMMIT;

-- ----------------------------
-- Table structure for `cleanmine_zaq`
-- ----------------------------
DROP TABLE IF EXISTS `cleanmine_zaq`;
CREATE TABLE `cleanmine_zaq` (
`win`  int(11) NOT NULL DEFAULT 0 COMMENT '统计总共胜局' ,
`total`  int(11) NOT NULL DEFAULT 0 COMMENT '统计总共玩的局数' ,
`minimum`  int(11) NULL DEFAULT NULL COMMENT '胜利一局所需要的最短时间,单位为秒' ,
`date`  date NULL DEFAULT NULL COMMENT '达成最短时间记录的日期' ,
`name`  varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT '' COMMENT '创纪录者姓名' ,
`winning_streak`  int(11) NOT NULL DEFAULT 0 COMMENT '最高连胜纪录' ,
`losing_streak`  int(11) NOT NULL DEFAULT 0 COMMENT '最高连败纪录' ,
`level`  char(20) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT '' COMMENT '级别,分为初级中级高级' ,
PRIMARY KEY (`level`)
)
ENGINE=InnoDB
DEFAULT CHARACTER SET=utf8 COLLATE=utf8_general_ci

;

-- ----------------------------
-- Records of cleanmine_zaq
-- ----------------------------
BEGIN;
COMMIT;

-- ----------------------------
-- Table structure for `goods`
-- ----------------------------
DROP TABLE IF EXISTS `goods`;
CREATE TABLE `goods` (
`goodsID`  char(10) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT 'G1001' COMMENT '商品编号' ,
`goodsName`  char(20) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '商品名称' ,
`goodsPrice`  double(10,0) NULL DEFAULT NULL COMMENT '商品单价' ,
`goodsImg`  char(30) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '商品图片' ,
`goodsintro`  varchar(200) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '商品描述' ,
PRIMARY KEY (`goodsID`)
)
ENGINE=InnoDB
DEFAULT CHARACTER SET=utf8 COLLATE=utf8_general_ci

;

-- ----------------------------
-- Records of goods
-- ----------------------------
BEGIN;
INSERT INTO `goods` VALUES ('D1001', '蜂蜜青瓜汁', '15', 'img/fmqg.jpg', '蜂蜜青瓜汁'), ('D1002', '贵妃果醋', '15', 'img/gfgc.jpg', '果醋'), ('D1003', '红柚小雀跃', '15', 'img/hyqy.jpg', '红柚汁'), ('D1004', '金桔百香果', '15', 'img/jjbx.jpg', '金桔百香果汁'), ('D1005', '蔓越莓之恋', '15', 'img/mym.jpg', '蔓越莓'), ('D1006', '柠檬老盐记', '16', 'img/nmgz.jpg', '柠檬果汁'), ('D1007', '苹果胡萝卜汁', '17', 'img/pghlb.jpg', '苹果胡萝卜汁'), ('D1008', '西瓜汁', '14', 'img/xgz.jpg', '西瓜汁'), ('D1009', '红柚青茶', '13', 'img/hyqc.jpg', '红柚青茶'), ('D2001', '圣杜卡酒庄歌海娜红葡萄酒', '110', 'img/ghnwine.jpg', '2012年'), ('D2002', '沙邦酒庄珍藏教皇新堡红葡萄酒', '234', 'img/jhxbwine.jpg', '2013年'), ('D2003', '吉佳乐世家圣约瑟夫红葡萄酒', '345', 'img/sysfwine.jpg', '2015年'), ('D2004', '嘉伯乐科瓦威园红葡萄酒', '321', 'img/kwwywine.jpg', '2015年'), ('D2005', '克丽丝塔士红葡萄酒', '232', 'img/klstwine.jpg', '2015年'), ('D2006', '吉恭达斯红葡萄酒', '431', 'img/jgdswine.jpg', '2016年'), ('D2007', '乌瑞亚酒庄瓦给拉斯红葡萄酒', '324', 'img/wglswine.jpg', '2016年'), ('F1001', '澳洲谷饲牛仔骨', '80', 'img/azgs.jpg', '采用澳洲牛仔骨，肉少但肉味更浓厚'), ('F1002', '谷饲厚切牛排', '79', 'img/gshq.jpg', '选自牛的腰脊部，原切牛排，肉质紧实，富有嚼劲，牛油香味浓郁，口感风味佳'), ('F1003', '欢乐儿童套餐', '50', 'img/hlet.jpg', '选自牛的腰脊部，原切工艺，原汁原味，营养丰富。本品仅献给身高1.4m以下儿童享用！'), ('F1004', '火焰牛排', '70', 'img/hynp.png', '选自牛的肩胛部，肉质厚实却又松嫩，与白兰地火焰的完美碰撞，兼具视觉与舌尖的完美体验'), ('F1005', '芥香火焰牛排', '75', 'img/jxhy.jpg', '选自牛的肋脊部，肉质油花分布较为均匀，肉质鲜嫩多汁。搭配蜂蜜芥末酱，口味清新'), ('F1006', '秘制川香风味牛排', '70', 'img/mzcx.jpg', '精选进口西冷牛排，肉质紧实，川味浓郁'), ('F1007', '苹果木烟熏厚切菲力', '89', 'img/gmfl.jpg', '特殊果木燃烧，烤味十足，香宜可口'), ('F1008', '沙朗牛排', '85', 'img/slnp.jpg', '选自于牛只肋脊部柔嫩的牛肉，油花多且分布均匀'), ('F1009', '勇士牛排', '70', 'img/ysnp.jpg', '选用牛肋骨末端，肉质鲜嫩多汁，搭配特殊酱汁，美味可口'), ('F1010', '西冷牛排', '81', 'img/xlnp.jpg', '选自前腰脊部，口感韧度强、油花嫩筋兼具，细致又丰腴，香甜多汁，有嚼感，因为富含肉汁'), ('F1011', '香草烤羊排', '68', 'img/xcyp.jpg', '选取羔羊肋排的“6根骨”，大厨选用香草多重的腌制，匠心呈现'), ('F1012', '香煎鸡排', '61', 'img/xjjp.jpg', '外焦里嫩，意犹未尽'), ('F1013', '阿文烤排', '99', 'img/awkp1.jpg', '招牌牛排，香的很'), ('F1014', '阿温烤排', '99', 'img/awkp2.jpg', '星厨推荐！！！'), ('F1015', '阿问烤排', '88', 'img/awkp2.jpg', '星厨推荐2！！！'), ('F2001', '奥尔良烤翅', '15', 'img/aokc.jpg', '奥尔良烤翅'), ('F2002', '格格薯格', '14', 'img/ggsg.jpg', '炸薯片'), ('F2003', '欢乐小吃组', '14', 'img/hlxc.jpg', '鸡翅鸡米花'), ('F2004', '黑虾球', '16', 'img/hxq.jpg', '虾球'), ('F2005', '吮指鱼球', '17', 'img/szyq.jpg', '鱼球');
COMMIT;

-- ----------------------------
-- Table structure for `manufacturer`
-- ----------------------------
DROP TABLE IF EXISTS `manufacturer`;
CREATE TABLE `manufacturer` (
`id`  int(11) NOT NULL AUTO_INCREMENT ,
`name`  varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL ,
PRIMARY KEY (`id`)
)
ENGINE=InnoDB
DEFAULT CHARACTER SET=utf8mb4 COLLATE=utf8mb4_bin
AUTO_INCREMENT=4

;

-- ----------------------------
-- Records of manufacturer
-- ----------------------------
BEGIN;
INSERT INTO `manufacturer` VALUES ('1', '华为'), ('2', '苹果'), ('3', 'vivo');
COMMIT;

-- ----------------------------
-- Table structure for `output`
-- ----------------------------
DROP TABLE IF EXISTS `output`;
CREATE TABLE `output` (
`output_id`  int(11) NOT NULL AUTO_INCREMENT ,
`output_count`  double NOT NULL COMMENT '金额' ,
`output_date`  datetime NOT NULL COMMENT '时间' ,
`output_kind`  varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL ,
`output_method`  varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL ,
`output_text`  text CHARACTER SET utf8 COLLATE utf8_general_ci NULL ,
PRIMARY KEY (`output_id`),
FOREIGN KEY (`output_kind`) REFERENCES `paykind` (`kind_name`) ON DELETE NO ACTION ON UPDATE CASCADE,
FOREIGN KEY (`output_method`) REFERENCES `paymethod` (`method_name`) ON DELETE CASCADE ON UPDATE CASCADE,
INDEX `种类` (`output_kind`) USING BTREE ,
INDEX `方式` (`output_method`) USING BTREE 
)
ENGINE=InnoDB
DEFAULT CHARACTER SET=utf8 COLLATE=utf8_general_ci
AUTO_INCREMENT=41

;

-- ----------------------------
-- Records of output
-- ----------------------------
BEGIN;
INSERT INTO `output` VALUES ('1', '250', '2020-11-18 00:00:00', '住宿', '花呗', null), ('3', '25.24', '2020-11-19 00:00:00', '零食', '支付宝', null), ('4', '50', '2020-11-16 00:00:00', '住宿', '花呗', null), ('5', '12.5', '2020-11-17 00:00:00', '零食', '支付宝', null), ('6', '12.5', '2020-11-17 00:00:00', '零食', '支付宝', null), ('7', '12.5', '2020-11-17 00:00:00', '零食', '支付宝', null), ('8', '10', '2020-11-05 00:00:00', '零食', '支付宝', null), ('16', '25', '2020-11-12 00:00:00', '水果啊啊啊', '现金', '2333'), ('18', '9999', '2020-11-20 00:00:00', '水果啊啊啊', '花呗', ''), ('19', '9999', '2020-11-20 00:00:00', '水果啊啊啊', '花呗', ''), ('20', '9999', '2020-11-20 13:31:16', '水果啊啊啊', '花呗', ''), ('21', '250', '2020-11-20 16:19:22', '零食', '微信', ''), ('22', '250', '2020-11-20 00:00:00', '零食', '微信', ''), ('23', '10', '2016-11-15 00:00:00', '水果啊啊啊', '支付宝', ''), ('24', '10', '2018-11-28 00:00:00', '水果啊啊啊', '现金', ''), ('25', '555', '2020-10-08 00:00:00', '衣服', '现金', '哭哭'), ('26', '123', '2020-11-20 00:00:00', '衣服', '支付宝', '666'), ('27', '1314', '2020-11-20 00:00:00', '水果啊啊啊', '支付宝', '520'), ('28', '12.5', '2020-11-20 00:00:00', '水果啊啊啊', '花呗', '5'), ('29', '5', '2020-11-20 00:00:00', '住宿', '支付宝', ''), ('30', '25', '2020-11-20 00:00:00', '住宿', '花呗', ''), ('31', '25', '2020-11-20 00:00:00', '住宿', '花呗', ''), ('32', '25', '2020-11-20 15:37:01', '住宿', '花呗', ''), ('33', '125', '2020-11-20 00:00:00', '住宿', '花呗', ''), ('34', '50', '2020-10-26 07:03:02', '水果啊啊啊', '花呗', ''), ('35', '20', '2020-11-20 16:15:31', '水果啊啊啊', '现金', ''), ('37', '50', '2020-11-21 10:35:38', '衣服', '现金', ''), ('38', '50', '2020-11-21 16:53:21', '水果啊啊啊', '支付宝', '发达了发达了'), ('39', '15', '2020-11-21 17:02:20', '水果啊啊啊', '现金', ''), ('40', '15.2', '2020-11-21 17:02:48', '衣服', '现金', '666');
COMMIT;

-- ----------------------------
-- Table structure for `paykind`
-- ----------------------------
DROP TABLE IF EXISTS `paykind`;
CREATE TABLE `paykind` (
`kind_id`  int(11) NOT NULL AUTO_INCREMENT ,
`kind_name`  varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT '' COMMENT '如：服饰，鞋帽。人情往来，过节红包等' ,
`kind_flag`  int(11) NOT NULL DEFAULT 2 COMMENT '-1支出，1收入' ,
PRIMARY KEY (`kind_id`),
INDEX `kind_name` (`kind_name`) USING BTREE ,
INDEX `kind_name_2` (`kind_name`, `kind_flag`) USING BTREE 
)
ENGINE=InnoDB
DEFAULT CHARACTER SET=utf8 COLLATE=utf8_general_ci
AUTO_INCREMENT=11

;

-- ----------------------------
-- Records of paykind
-- ----------------------------
BEGIN;
INSERT INTO `paykind` VALUES ('4', '住宿', '2'), ('6', '奖金', '1'), ('10', '彩票中奖', '1'), ('7', '提现', '1'), ('1', '水果啊啊啊', '2'), ('8', '电费', '2'), ('5', '红包没有辽', '1'), ('3', '衣服', '2'), ('2', '零食', '2'), ('9', '饭卡充值', '2');
COMMIT;

-- ----------------------------
-- Table structure for `paymethod`
-- ----------------------------
DROP TABLE IF EXISTS `paymethod`;
CREATE TABLE `paymethod` (
`method_id`  int(11) NOT NULL AUTO_INCREMENT ,
`method_name`  varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '如：支付宝，现金，微信等' ,
PRIMARY KEY (`method_id`),
INDEX `method_name` (`method_name`) USING BTREE 
)
ENGINE=InnoDB
DEFAULT CHARACTER SET=utf8 COLLATE=utf8_general_ci
AUTO_INCREMENT=7

;

-- ----------------------------
-- Records of paymethod
-- ----------------------------
BEGIN;
INSERT INTO `paymethod` VALUES ('4', '云闪付'), ('3', '微信'), ('2', '支付宝'), ('1', '现金'), ('5', '花呗'), ('6', '银行卡');
COMMIT;

-- ----------------------------
-- Table structure for `post`
-- ----------------------------
DROP TABLE IF EXISTS `post`;
CREATE TABLE `post` (
`id`  int(11) NOT NULL AUTO_INCREMENT ,
`content`  varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL ,
`dob`  date NULL DEFAULT NULL ,
`blog_id`  int(11) NULL DEFAULT NULL ,
PRIMARY KEY (`id`)
)
ENGINE=InnoDB
DEFAULT CHARACTER SET=utf8mb4 COLLATE=utf8mb4_bin
AUTO_INCREMENT=3

;

-- ----------------------------
-- Records of post
-- ----------------------------
BEGIN;
INSERT INTO `post` VALUES ('1', 'a post', '2020-10-21', '1'), ('2', 'b post', '2020-10-19', '1');
COMMIT;

-- ----------------------------
-- Table structure for `product`
-- ----------------------------
DROP TABLE IF EXISTS `product`;
CREATE TABLE `product` (
`id`  int(11) NOT NULL AUTO_INCREMENT ,
`name`  varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL ,
`price`  float(10,2) NULL DEFAULT NULL ,
`image`  varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL ,
`description`  text CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL ,
`manufacturer`  int(11) NULL DEFAULT NULL ,
PRIMARY KEY (`id`)
)
ENGINE=InnoDB
DEFAULT CHARACTER SET=utf8mb4 COLLATE=utf8mb4_general_ci
AUTO_INCREMENT=4

;

-- ----------------------------
-- Records of product
-- ----------------------------
BEGIN;
INSERT INTO `product` VALUES ('1', 'matepro40', '8888.00', 'mate40.png', 'mate40的描述', '1'), ('2', 'iphone11', '9999.00', 'iphone11.png', 'iphone11的描述', '2'), ('3', 'vivo2020', '7777.00', 'vovo.png', 'vivo的手机描述', '3');
COMMIT;

-- ----------------------------
-- Table structure for `saolei`
-- ----------------------------
DROP TABLE IF EXISTS `saolei`;
CREATE TABLE `saolei` (
`User_name`  char(20) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT '' COMMENT '用户名' ,
`User_date`  date NULL DEFAULT NULL COMMENT '记录创建时间' ,
`User_minimum`  int(11) NULL DEFAULT NULL COMMENT '记录时间,单位秒' ,
`level`  int(11) NULL DEFAULT NULL COMMENT '难度等级:1,2,3' 
)
ENGINE=InnoDB
DEFAULT CHARACTER SET=utf8 COLLATE=utf8_general_ci

;

-- ----------------------------
-- Records of saolei
-- ----------------------------
BEGIN;
INSERT INTO `saolei` VALUES ('aaa', '2020-09-25', '5', '1'), ('aaa', '2020-09-25', '50', '1'), ('aaa', '2020-09-25', '60', '1'), ('aaa', '2020-09-25', '150', '3'), ('aaa', '2020-09-25', '25', '1'), ('aaa', '2020-09-25', '30', '2');
COMMIT;

-- ----------------------------
-- Table structure for `users`
-- ----------------------------
DROP TABLE IF EXISTS `users`;
CREATE TABLE `users` (
`usersID`  char(10) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT 'U1001' COMMENT '用户ID' ,
`usersName`  char(20) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT '张三' COMMENT '用户名' ,
`usersPassword`  char(30) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT '123456' COMMENT '密码' ,
`phone`  char(30) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '联系方式' ,
PRIMARY KEY (`usersName`)
)
ENGINE=InnoDB
DEFAULT CHARACTER SET=utf8 COLLATE=utf8_general_ci

;

-- ----------------------------
-- Records of users
-- ----------------------------
BEGIN;
INSERT INTO `users` VALUES ('', '兰小斌', 'lanxiaobin1*', ''), ('', '哈哈', '123456', ''), ('', '喜羊羊', '123456', ''), ('', '欧得姚', 'oudeyao1*', ''), ('001', '谢思文', 'xiesiwen1*', '18250601907');
COMMIT;

-- ----------------------------
-- Procedure structure for `PRO_ASP_USER_LOGIN`
-- ----------------------------
DROP PROCEDURE IF EXISTS `PRO_ASP_USER_LOGIN`;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `PRO_ASP_USER_LOGIN`(IN i_login VARCHAR(20),IN i_pwd VARCHAR(20),OUT o_code INT,OUT o_note VARCHAR(100))
    COMMENT '20210312        zaq         新建存储过程，输入用户名和密码，校验后返回结果'
BEGIN
  DECLARE  v_exists INT;
  DECLARE  v_pwd    varchar(20);

  SELECT 1 into v_exists FROM asp_user WHERE EXISTS(SELECT u_id FROM asp_user WHERE U_LOGIN=i_login and `DELETE`=0) and `DELETE`=0 LIMIT 1;
  IF v_exists=NULL THEN
    SET o_code=-1;
    SET o_note=v_login||"用户不存在";
  ELSE
    SELECT u_pwd into v_pwd FROM asp_user WHERE u_login=i_login and `DELETE`=0 LIMIT 1;
    IF  v_pwd=i_pwd THEN
      SET o_code=1;
      SET o_note="登录成功";
      SELECT u_id,u_login,u_name,null U_PWD,U_PLANET,U_CREATEDATE,U_BIRTHDAY,U_SEX,U_STATUS,`DELETE` FROM asp_user where U_LOGIN=i_login and `DELETE`=0;
    ELSE
      SET o_code=-1;
      SET o_note="用户名或密码错误";
    END IF;
  END IF;
END
;;
DELIMITER ;

-- ----------------------------
-- Auto increment value for `asp_book`
-- ----------------------------
ALTER TABLE `asp_book` AUTO_INCREMENT=1;

-- ----------------------------
-- Auto increment value for `asp_calendar`
-- ----------------------------
ALTER TABLE `asp_calendar` AUTO_INCREMENT=1;

-- ----------------------------
-- Auto increment value for `asp_kind_book`
-- ----------------------------
ALTER TABLE `asp_kind_book` AUTO_INCREMENT=2;

-- ----------------------------
-- Auto increment value for `asp_kind_kind`
-- ----------------------------
ALTER TABLE `asp_kind_kind` AUTO_INCREMENT=14;

-- ----------------------------
-- Auto increment value for `asp_kind_pay`
-- ----------------------------
ALTER TABLE `asp_kind_pay` AUTO_INCREMENT=5;

-- ----------------------------
-- Auto increment value for `asp_kind_text`
-- ----------------------------
ALTER TABLE `asp_kind_text` AUTO_INCREMENT=4;

-- ----------------------------
-- Auto increment value for `asp_statement`
-- ----------------------------
ALTER TABLE `asp_statement` AUTO_INCREMENT=1;

-- ----------------------------
-- Auto increment value for `asp_text`
-- ----------------------------
ALTER TABLE `asp_text` AUTO_INCREMENT=4;

-- ----------------------------
-- Auto increment value for `asp_user`
-- ----------------------------
ALTER TABLE `asp_user` AUTO_INCREMENT=3;

-- ----------------------------
-- Auto increment value for `author`
-- ----------------------------
ALTER TABLE `author` AUTO_INCREMENT=4;

-- ----------------------------
-- Auto increment value for `blog`
-- ----------------------------
ALTER TABLE `blog` AUTO_INCREMENT=3;

-- ----------------------------
-- Auto increment value for `bookkeeping`
-- ----------------------------
ALTER TABLE `bookkeeping` AUTO_INCREMENT=15;

-- ----------------------------
-- Auto increment value for `manufacturer`
-- ----------------------------
ALTER TABLE `manufacturer` AUTO_INCREMENT=4;

-- ----------------------------
-- Auto increment value for `output`
-- ----------------------------
ALTER TABLE `output` AUTO_INCREMENT=41;

-- ----------------------------
-- Auto increment value for `paykind`
-- ----------------------------
ALTER TABLE `paykind` AUTO_INCREMENT=11;

-- ----------------------------
-- Auto increment value for `paymethod`
-- ----------------------------
ALTER TABLE `paymethod` AUTO_INCREMENT=7;

-- ----------------------------
-- Auto increment value for `post`
-- ----------------------------
ALTER TABLE `post` AUTO_INCREMENT=3;

-- ----------------------------
-- Auto increment value for `product`
-- ----------------------------
ALTER TABLE `product` AUTO_INCREMENT=4;
